package com.kuhh.dao.impl;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.kuhh.dao.IStudentsDao;
import com.kuhh.pojo.Students;
import com.kuhh.utils.JdbcUtils;

public class StudentsDaoImpl implements IStudentsDao{

	private Connection conn;
	private PreparedStatement ps;
	private ResultSet rs;
	
	@Override
	public List<Students> getStudentList() {
		// TODO Auto-generated method stub
		List<Students> list = new ArrayList<Students>();
		
		try {
			conn = JdbcUtils.getConnection();
			String sql = "select * from tb_students order by create_time desc";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				Students student = new Students();
				student.setStudentNo(rs.getString(1));
				student.setStudentName(rs.getString(2));
				student.setGradeNo(rs.getString(3));
				student.setCollegeNo(rs.getString(4));
				student.setProfessionNo(rs.getString(5));
				student.setClassNo(rs.getString(6));
				student.setDescription(rs.getString(7));
				student.setIdCard(rs.getString(8));
				student.setAge(rs.getInt(9));
				student.setGender(rs.getByte(10));
				student.setYear(rs.getString(11));
				student.setCreateTime(rs.getDate(12));
				student.setUpdateTime(rs.getDate(13));
				list.add(student);
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcUtils.close(conn, ps, rs);
		}
		return list;
	}

	@Override
	public Students getStudentByNo(String studentNo) {
		// TODO Auto-generated method stub
		try {
			conn = JdbcUtils.getConnection();
			String sql = "select * from tb_students where student_no = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, studentNo);
			rs = ps.executeQuery();
			while(rs.next()) {
				Students student = new Students();
				student.setStudentNo(rs.getString(1));
				student.setStudentName(rs.getString(2));
				student.setGradeNo(rs.getString(3));
				student.setCollegeNo(rs.getString(4));
				student.setProfessionNo(rs.getString(5));
				student.setClassNo(rs.getString(6));
				student.setDescription(rs.getString(7));
				student.setIdCard(rs.getString(8));
				student.setAge(rs.getInt(9));
				student.setGender(rs.getByte(10));
				student.setYear(rs.getString(11));
				student.setCreateTime(rs.getDate(12));
				student.setUpdateTime(rs.getDate(13));
				return student;
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcUtils.close(conn, ps, rs);
		}
		return null;
	}

	@Override
	public boolean addStudent(Students student) {
		// TODO Auto-generated method stub
		try {
			conn = JdbcUtils.getConnection();
			String sql = "insert into tb_students values(?,?,?,?,?,?,?,?,?,?,?,?,?)";
			ps = conn.prepareStatement(sql);
			ps.setString(1, student.getStudentNo());
			ps.setString(2, student.getStudentName());
			ps.setString(3, student.getGradeNo());
			ps.setString(4, student.getCollegeNo());
			ps.setString(5, student.getProfessionNo());
			ps.setString(6, student.getClassNo());
			ps.setString(7, student.getDescription());
			ps.setString(8, student.getIdCard());
			ps.setInt(9, student.getAge());
			ps.setByte(10, student.getGender());
			ps.setString(11, student.getYear());
			ps.setDate(12, new Date(student.getCreateTime().getTime()));
			ps.setDate(13, new Date(student.getUpdateTime().getTime()));
			ps.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcUtils.close(conn, ps, rs);
		}
		return false;
	}

	@Override
	public boolean updateStudent(Students student) {
		// TODO Auto-generated method stub
		try {
			conn = JdbcUtils.getConnection();
			String sql = "update tb_students set student_name = ?,grade_no = ?,college_no = ?,"
					+ "profession_no = ?,class_no = ?,description = ?,id_card = ?,"
					+ "age = ?,gender = ?,year = ?,update_time = ? "
					+ "where student_no = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(12, student.getStudentNo());
			ps.setString(1, student.getStudentName());
			ps.setString(2, student.getGradeNo());
			ps.setString(3, student.getCollegeNo());
			ps.setString(4, student.getProfessionNo());
			ps.setString(5, student.getClassNo());
			ps.setString(6, student.getDescription());
			ps.setString(7, student.getIdCard());
			ps.setInt(8, student.getAge());
			ps.setByte(9, student.getGender());
			ps.setString(10, student.getYear());
			ps.setDate(11, new Date(student.getUpdateTime().getTime()));
			ps.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcUtils.close(conn, ps, rs);
		}
		return false;
	}

	@Override
	public boolean deleteStudent(String studentNo) {
		// TODO Auto-generated method stub
		try {
			conn = JdbcUtils.getConnection();
			String sql = "delete from tb_students where student_no = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, studentNo);
			ps.executeUpdate();
			return true;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcUtils.close(conn, ps, rs);
		}
		return false;
	}

	@Override
	public List<Students> getStudentUnRegister() {
		// TODO Auto-generated method stub
		List<Students> list = new ArrayList<>();
		try {
			conn = JdbcUtils.getConnection();
			String sql = "select * from tb_students where student_no not in (select student_teacher_no from tb_user) order by create_time desc";
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while(rs.next()) {
				Students student = new Students();
				student.setStudentNo(rs.getString(1));
				student.setStudentName(rs.getString(2));
				student.setGradeNo(rs.getString(3));
				student.setCollegeNo(rs.getString(4));
				student.setProfessionNo(rs.getString(5));
				student.setClassNo(rs.getString(6));
				student.setDescription(rs.getString(7));
				student.setIdCard(rs.getString(8));
				student.setAge(rs.getInt(9));
				student.setGender(rs.getByte(10));
				student.setYear(rs.getString(11));
				student.setCreateTime(rs.getDate(12));
				student.setUpdateTime(rs.getDate(13));
				list.add(student);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcUtils.close(conn, ps, rs);
		} 
		return list;
	}

	@Override
	public void updateStudent(String studentNo, String description) {
		// TODO Auto-generated method stub
		try {
			conn = JdbcUtils.getConnection();
			String sql = "update tb_students set description = ?,update_time where student_no = ?";
			ps = conn.prepareStatement(sql);
			ps.setString(1, description);
			ps.setDate(2, new Date(new java.util.Date().getTime()));
			ps.setString(3, studentNo);
			ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			JdbcUtils.close(conn, ps, rs);
		}
	}

}
